from rest_framework.views import APIView
from rest_framework.viewsets import ModelViewSet
from rest_framework.response import Response
from rest_framework import status
from django.utils import timezone
import jwt, rsa
from django.shortcuts import render
from django.http import StreamingHttpResponse
from django.utils.encoding import escape_uri_path

from equity_admin.serializers_users import *
from equity_mall.utils.common_msg import common_msg
from equity_admin.tasks import *
from equity_mall.utils.pages import MyPage


class SmsCodeView(APIView):
    '''管理员短信验证码'''

    permission_classes = ()

    def get(self, request, mobile):
        user = User.objects.filter(mobile=mobile, is_active=True, role_id=1)
        if not user:
            return Response({'error': '该手机号码尚未注册!'}, status=status.HTTP_400_BAD_REQUEST)
        redis_conn = get_redis_connection("verify_code")
        send_flag = redis_conn.get("send_flag_%s" % mobile)
        if send_flag:
            return Response({'error': '发送短信过于频繁!'}, status=status.HTTP_400_BAD_REQUEST)
        sms_code = '%06d' % randint(0, 999999)
        logger.info(sms_code)
        pl = redis_conn.pipeline()
        pl.setex("sms_%s" % mobile, 300, sms_code)
        pl.setex('send_flag_%s' % mobile, 60, 1)
        pl.execute()
        content = f'【微邮付】登录验证码为：{sms_code}，若非本人操作，请忽略。'
        result = common_msg(mobile, content)
        return Response({"errmsg": "发送短信验证码成功"}, status=status.HTTP_200_OK)


class LevelModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name', 'account')
    filter_class = LevelFilter

    def get_serializer_class(self):
        if self.action == 'list':
            return LevelListSerializer
        else:
            return LevelRetrieveSerializer

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        goods_id = self.request.query_params.get('goods_id', None)
        activity_id = self.request.query_params.get('activity_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = Level.objects.all().order_by('level')
        elif level.level == 1:
            queryset = Level.objects.filter(prov_id=level.id).order_by('level')
        elif level.level == 2:
            queryset = Level.objects.filter(city_id=level.id).order_by('level')
        elif level.level == 3:
            queryset = Level.objects.filter(district_id=level.id).order_by('level')
        else:
            queryset = Level.objects.filter(branch_id=level.id).order_by('level')
        if goods_id:
            goods = Goods.objects.filter(id=goods_id).first()
            if goods:
                queryset = queryset.filter(merchantenter__merchant_id=goods.merchant_id).exclude(launchedgoods__goods_id=goods_id).filter(level=4)
        if activity_id:
            level_id_list = list(AliDiscountCouponLevel.objects.filter(activity_id=activity_id).values_list('level_id', flat=True))
            queryset = queryset.filter(id__in=level_id_list)
        return queryset

    def export_level(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        search = self.request.query_params.get('search', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE level!=0'
        elif level.level == 1:
            condition = f'WHERE prov_id={level.id}'
        elif level.level == 2:
            condition = f'WHERE city_id={level.id}'
        elif level.level == 3:
            condition = f'WHERE district_id={level.id}'
        else:
            condition = f'WHERE branch_id={level.id}'
        if search:
            condition = condition + f' AND (name LIKE "%{search}%" OR account LIKE "%{search}%" )'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT * FROM tb_u_level {condition};
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT * FROM tb_u_level;
            '''
            cursor.execute(sql)
        ziduan = ['id', '机构名称', '组织机构id', '微邮付机构id', '机构号(8位)', '机构号(9位)', '省', '市', '区/县', '网点', '地址', '经度', '维度']
        cols = ['id', 'name', 'orgId', 'acceptId', 'account', 'account_nine', 'prov_name', 'city_name', 'district_name', 'branch_name', 'address', 'longitude', 'latitude']
        response = StreamingHttpResponse(self.get_level_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('订单列表.csv'))
        return response

    def get_level_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['orgId', 'account']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        longitude = self.request.data.get('longitude', None)
        latitude = self.request.data.get('latitude', None)
        address = self.request.data.get('address', None)
        telephone = self.request.data.get('telephone', None)
        if longitude and latitude:
            instance.latitude = latitude
            instance.longitude = longitude
        if address:
            instance.address = address
        if telephone:
            instance.telephone = telephone
        instance.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class UserLoginView(APIView):
    '''管理员登录'''

    permission_classes = ()

    def post(self, request):
        mobile = self.request.data.get('mobile', None)
        sms_code = self.request.data.get('sms_code', None)
        if not all([mobile, sms_code]):
            return Response({'error': '缺少必传参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            user = User.objects.get(mobile=mobile, is_active=True, role_id=1)
        except:
            return Response({'error': '该手机号码不属于有效用户!'}, status=status.HTTP_400_BAD_REQUEST)
        if str(mobile) == '13510535265':
            dic = {
                'exp': timezone.now() + datetime.timedelta(days=30),
                'iat': timezone.now(),
                'iss': 'xinxiang',
                'data': {
                    'mobile_phone': mobile,
                    'id': user.id,
                    'username': user.username,
                    'nickname': user.nickname
                },
            }
            jwt_token = jwt.encode(dic, settings.SECRET_KEY, algorithm='HS256')
            return Response({
                'username': user.username,
                'user_id': user.id,
                'token': jwt_token,
                'mobile': user.mobile
            }, status=status.HTTP_200_OK)
        redis_conn = get_redis_connection("verify_code")
        black_mobile_server = redis_conn.get("black_%s" % mobile)
        if black_mobile_server:
            black_mobile_server = int(black_mobile_server.decode())
            if black_mobile_server == 5:
                return Response({'error': "当前账号已被冻结"}, status=status.HTTP_400_BAD_REQUEST)
        else:
            black_mobile_server = 0
        sms_code_server = redis_conn.get("sms_%s" % mobile)
        if sms_code_server is None:
            return Response({'error': "短信验证码失效"}, status=status.HTTP_400_BAD_REQUEST)
        sms_code_server = sms_code_server.decode()
        if sms_code_server != str(sms_code):
            black_mobile_server += 1
            redis_conn.setex("black_%s" % mobile, 86400, str(black_mobile_server))
            return Response({'error': "输入验证码有误"}, status=status.HTTP_400_BAD_REQUEST)
        redis_conn.delete("sms_%s" % mobile)
        redis_conn.delete("black_%s" % mobile)
        if not user.is_active:
            return Response({'error': '用户尚未激活!'}, status=status.HTTP_400_BAD_REQUEST)
        dic = {
            'exp': timezone.now() + datetime.timedelta(days=30),
            'iat': timezone.now(),
            'iss': 'xinxiang',
            'data': {
                'mobile_phone': mobile,
                'id': user.id,
                'username': user.username,
                'nickname': user.nickname
            },
        }
        jwt_token = jwt.encode(dic, settings.SECRET_KEY, algorithm='HS256')
        return Response({
            'username': user.username,
            'user_id': user.id,
            'token': jwt_token,
            'mobile': user.mobile
        }, status=status.HTTP_200_OK)


class UserModelViewSet(ModelViewSet):
    '''登录用户'''

    def info(self, request):
        user = self.request.iser
        data = UserInfoSerializer(user).data
        return Response(data, status=status.HTTP_200_OK)

    def aaaa(self, request):
        return render(request, 'jump_applet.html')

    def wxpay_cookie(self, request):
        url = 'http://47.107.132.8:8000/wxpay/'
        id = self.request.query_params.get('id', None)
        pwc = Channel.objects.get(id=id)
        url = f'http://47.107.132.8:8000/wxpay/?mchid={pwc.mchid}'
        try:
            response = requests.get(url)
            cookies_dict = response.json()
            if 'error' in cookies_dict:
                return Response({'error': '长时间未登录!'}, status=status.HTTP_400_BAD_REQUEST)
            ecc_csrf_token = cookies_dict['ecc_csrf_cookie']
            cookie = ''
            for key, value in cookies_dict.items():
                a = f'{key}={value};'
                cookie += a
            pwc.cookie = cookie
            pwc.ecc_csrf_token = ecc_csrf_token
            pwc.save()
            return Response({'success': '更新成功!'}, status=status.HTTP_200_OK)
        except Exception as e:
            return Response({'error': '更新失败!', 'yy': e}, status=status.HTTP_400_BAD_REQUEST)


class CustomerUserModelViewSet(ModelViewSet):
    '''C端用户'''

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('username', 'mobile', 'realname')
    filter_class = CustomerUserFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            return CustomerUser.objects.all().order_by('-create_time')
        elif level.level == 1:
            return CustomerUser.objects.filter(level__prov_id=level.id).order_by('-create_time')
        elif level.level == 2:
            return CustomerUser.objects.filter(level__city_id=level.id).order_by('-create_time')
        elif level.level == 3:
            return CustomerUser.objects.filter(level__district_id=level.id).order_by('-create_time')
        else:
            return CustomerUser.objects.filter(level__branch_id=level.id).order_by('-create_time')

    def get_serializer_class(self):
        if self.action == 'list':
            return CustomerUserListModelSerializer
        else:
            return CustomerUserRetrieveModelSerializer

    def customeruser_point_record(self, request):
        customeruser_id = self.request.query_params.get('customeruser_id', None)
        if not customeruser_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=customeruser_id)
        except:
            return Response({'error': '无效参数'}, status=status.HTTP_400_BAD_REQUEST)
        queryset = instance.pointrecord_set.all()
        page = self.paginate_queryset(queryset)
        data = CustomerUserPointRecordModelSerializer(page, many=True).data
        return self.get_paginated_response(data)

    def export_customeruser(self, request):
        user = self.request.iser
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        level_id = self.request.query_params.get('level', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE level_id IN (SELECT id FROM tb_u_level)'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (username LIKE "%{search}%" OR realname LIKE "%{search}%" OR mobile LIKE "%{search}%")'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.prov_name,city_name,district_name,branch_name FROM (SELECT a.*,b.username FROM (SELECT id,mobile,realname,idcode,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,user_id,level_id FROM tb_u_customer_user {condition})AS a LEFT JOIN (SELECT id,username FROM tb_u_user)AS b ON a.user_id=b.id)AS c LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT c.*,d.prov_name,city_name,district_name,branch_name FROM (SELECT a.*,b.username FROM (SELECT id,mobile,realname,idcode,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,user_id,level_id FROM tb_u_customer_user)AS a LEFT JOIN (SELECT id,username FROM tb_u_user)AS b ON a.user_id=b.id)AS c LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '手机号', '真实姓名', '身份证号码', '注册时间', '所属客户经理', '归属省公司', '归属市公司', '归属区县', '归属网点']
        cols = ['id', 'mobile', 'realname', 'idcode', 'create_time', 'username', 'prov_name', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_customeruser_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('月月免单领取明细.csv'))
        return response

    def get_customeruser_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                # if col == 'trade_type':
                #     tmp_str += '"%s",' % (TRADE_TYPE_ENUM[row[col]])
                # elif col == 'payment_state':
                #     tmp_str += '"%s",' % (PAYMENT_STATE_ENUM[row[col]])
                # # elif col == 'amount':
                # #     if row['payment_state'] in [1, 2, 3]:
                # #         tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                # #     else:
                # #         tmp_str += '"%s",' % ''
                # elif col in ['payment_no', 'trade_no']:
                #     tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                # else:
                tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class ManagerUserModelViewSet(ModelViewSet):
    '''客户经理'''

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('username', 'mobile', 'nickname', 'account')
    filter_class = ManagerUserFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = User.objects.all().order_by('-create_time')
        elif level.level == 1:
            queryset = User.objects.filter(level__prov_id=level.id).order_by('-create_time')
        elif level.level == 2:
            queryset = User.objects.filter(level__city_id=level.id).order_by('-create_time')
        elif level.level == 3:
            queryset = User.objects.filter(level__district_id=level.id).order_by('-create_time')
        else:
            queryset = User.objects.filter(level__branch_id=level.id).order_by('-create_time')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return ManagerUserListModelSerializer
        else:
            return ManagerUserRetrieveModelSerializer

    def create(self, request, *args, **kwargs):
        user = self.request.iser
        level = user.level
        mobile = self.request.data.get('mobile', None)
        username = self.request.data.get('username', None)
        role_id = self.request.data.get('role_id', None)
        is_active = self.request.data.get('is_active', None)
        level_id = self.request.data.get('level_id', None)
        if not all([mobile, username, role_id, is_active, level_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            level_list = get_level_list(level)
            level_id = int(level_id)
            if level_id not in level_list:
                return Response({'error': '无效机构id'}, status=status.HTTP_400_BAD_REQUEST)
        except:
            return Response({'error': '无效机构id'}, status=status.HTTP_400_BAD_REQUEST)
        if str(role_id) not in ['2', '3', '4', '5']:
            return Response({'error': '无效角色id'}, status=status.HTTP_400_BAD_REQUEST)
        if str(is_active) not in ['0', '1']:
            return Response({'error': '状态参数有误'}, status=status.HTTP_400_BAD_REQUEST)
        if not re.match(r'^1[3-9]\d{9}$', mobile):
            return Response({"error": "您输入的手机号格式不正确"}, status=status.HTTP_400_BAD_REQUEST)
        user_data = {}
        user_data['mobile'] = mobile
        user_data['username'] = username
        user_data['nickname'] = username
        user_data['role_id'] = role_id
        user_data['is_active'] = is_active
        user_data['level_id'] = level_id
        user_data['create_time'] = datetime.datetime.now()
        user_data['create_user_id'] = user.id
        try:
            user, create = User.objects.get_or_create(defaults=user_data, mobile=mobile)
            if create:
                return Response({'success': '添加成功', 'id': user.id}, status=status.HTTP_200_OK)
            else:
                return Response({'success': '重复添加', 'id': user.id}, status=status.HTTP_200_OK)
        except:
            return Response({'error': '添加失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        user = self.request.iser
        level = user.level
        mobile = self.request.data.get('mobile', None)
        username = self.request.data.get('username', None)
        role_id = self.request.data.get('role_id', None)
        is_active = self.request.data.get('is_active', '0')
        level_id = self.request.data.get('level_id', None)
        is_developer = self.request.data.get('is_developer', '0')
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id not in level_list:
                    return Response({'error': '无效机构id'}, status=status.HTTP_400_BAD_REQUEST)
            except:
                return Response({'error': '无效机构id'}, status=status.HTTP_400_BAD_REQUEST)
            instance.level_id = level_id
        if str(is_active):
            if str(is_active) not in ['0', '1']:
                return Response({'error': '状态参数有误'}, status=status.HTTP_400_BAD_REQUEST)
            instance.is_active = is_active
        if str(is_developer):
            if str(is_developer) not in ['0', '1']:
                return Response({'error': '开发者参数有误'}, status=status.HTTP_400_BAD_REQUEST)
            instance.is_developer = is_developer
            if str(is_developer) == '1':
                if not instance.public_key:
                    public_key, private_key = rsa.newkeys(2048)
                    rsa_pem_public_key = public_key.save_pkcs1().decode("utf-8")
                    rsa_der_public_key = private_key.save_pkcs1().decode("utf-8")
                    authen_key = random_str(32)
                    instance.authen_key = authen_key
                    instance.public_key = rsa_pem_public_key
                    instance.private_key = rsa_der_public_key
        if role_id:
            if user.id != 1:
                if str(role_id) not in ['2', '3', '4', '5']:
                    return Response({'error': '无效角色id'}, status=status.HTTP_400_BAD_REQUEST)
            instance.role_id = role_id
        if mobile and mobile != instance.mobile:
            if not re.match(r'^1[3-9]\d{9}$', mobile):
                return Response({"error": "您输入的手机号格式不正确"}, status=status.HTTP_400_BAD_REQUEST)
            user = User.objects.filter(mobile=mobile)
            if user:
                return Response({"error": "手机号码重复"}, status=status.HTTP_400_BAD_REQUEST)
            instance.mobile = mobile
        if username:
            instance.username = username
            instance.nickname = username
        instance.save()
        return Response({"success": "修改成功"}, status=status.HTTP_200_OK)

    def export_manageruser(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        is_active = self.request.query_params.get('is_active', None)
        role_id = self.request.query_params.get('role_id', None)
        search = self.request.query_params.get('search', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id!=1'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (username LIKE "%{search}%" OR mobile LIKE "%{search}%" OR nickname LIKE "%{search}%")'
        if is_active:
            condition = condition + f' AND is_active IN ({is_active})'
        if role_id:
            condition = condition + f' AND role_id IN ({role_id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'''
        SELECT e.*,f.create_username FROM (SELECT c.*,d.role_name FROM (SELECT a.*,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,username,mobile,is_active,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,level_id,role_id,create_user_id FROM tb_u_user {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT id,name AS role_name FROM tb_u_role)AS d ON c.role_id=d.id)AS e LEFT JOIN (SELECT id,username AS create_username FROM tb_u_user)AS f ON e.create_user_id=f.id;
        '''
        cursor.execute(sql)
        ziduan = ['id', '角色', '在离岗状态', '姓名', '手机号码', '所属网点', '所属区县', '所属市公司', '所属省公司', '创建人', '创建时间']
        cols = ['id', 'role_name', 'is_active', 'username', 'mobile', 'branch_name', 'district_name', 'city_name', 'prov_name', 'create_username', 'create_time']
        response = StreamingHttpResponse(self.get_manageruser_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('订单列表.csv'))
        return response

    def get_manageruser_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_ACTIVE_ENUM = {
            0: '离岗',
            1: '在岗',
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_active':
                    tmp_str += '"%s",' % (IS_ACTIVE_ENUM[row[col]])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class CustomerUserViceModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('mobile', 'unionid')
    filter_class = CustomerUserViceFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        sort = self.request.query_params.get('sort', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CustomerUserVice.objects.all()
        elif level.level == 1:
            queryset = CustomerUserVice.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CustomerUserVice.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CustomerUserVice.objects.filter(level__district_id=level.id)
        else:
            queryset = CustomerUserVice.objects.filter(level__branch_id=level.id)
        if sort == '1':
            queryset = queryset.order_by('point')
        if sort == '2':
            queryset = queryset.order_by('-point')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return CustomerUserViceListModelSerializer
        else:
            return CustomerUserViceRetrieveModelSerializer

    def customeruservice_order(self, request):
        try:
            level_id = self.request.query_params.get('level_id', None)
            customeruservice_id = self.request.query_params.get('customeruservice_id', None)
            bank_type = self.request.query_params.get('bank_type', None)
            pay_time_begin = self.request.query_params.get('pay_time_begin', None)
            pay_time_end = self.request.query_params.get('pay_time_end', None)
            amount_min = self.request.query_params.get('amount_min', None)
            amount_max = self.request.query_params.get('amount_max', None)
            search = self.request.query_params.get('search', None)
            page = int(self.request.query_params.get('page', 1))
            pagesize = int(self.request.query_params.get('pagesize', 10))
            if not customeruservice_id:
                return Response({'error': '请上传所属用户id'}, status=status.HTTP_400_BAD_REQUEST)
            try:
                customeruservice = CustomerUserVice.objects.get(id=customeruservice_id)
            except:
                return Response({'error': '无效用户id'}, status=status.HTTP_400_BAD_REQUEST)
            condition = f'WHERE customeruservice_id={customeruservice_id}'
            if level_id:
                level = Level.objects.filter(id=level_id).first()
                level_list = get_level_list(level)
                level_tuple = tuple(level_list)
                if len(level_tuple) == 1:
                    level_tuple = str(level_tuple).replace(',', '')
                condition = condition + f' AND level_id IN {level_tuple}'
            if pay_time_begin:
                condition = condition + f' AND pay_time >= "{pay_time_begin + " 00:00:00"}"'
            if pay_time_end:
                condition = condition + f' AND pay_time <= "{pay_time_end + " 23:59:59"}"'
            if amount_min:
                condition = condition + f' AND money>={amount_min}'
            if amount_max:
                condition = condition + f' AND money<={amount_max}'
            if bank_type:
                condition = condition + f' AND bank_type_id IN ({bank_type})'
            if search:
                merchant_list = Merchant.objects.filter(name__contains=search).values_list('id', flat=True)
                if merchant_list:
                    merchant_tuple = tuple(merchant_list)
                    if len(merchant_tuple) == 1:
                        merchant_tuple = str(merchant_tuple).replace(',', '')
                    condition = condition + f' AND merchant_id IN {merchant_tuple}'
            conn = pymysql.connect(host=settings.DATABASES['business_custmoer_order_data']['HOST'],
                                   port=settings.DATABASES['business_custmoer_order_data']['PORT'],
                                   database=settings.DATABASES['business_custmoer_order_data']['NAME'],
                                   user=settings.DATABASES['business_custmoer_order_data']['USER'],
                                   password=settings.DATABASES['business_custmoer_order_data']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            sql = f'''
            SELECT COUNT(id) AS counts FROM tb_o_point_order_info_{customeruservice.id%900} {condition};
            '''
            cursor.execute(sql)
            row = cursor.fetchone()
            counts = row['counts']
            pages = math.ceil(counts/pagesize)
            if pages == 0:
                pages = 1
            if page > pages:
                return Response({'error': '无效页面'}, status=status.HTTP_400_BAD_REQUEST)
            condition = condition + f' ORDER BY -id LIMIT {(page-1)*pagesize},{pagesize}'
            sql = f'''
            SELECT id,DATE_FORMAT(pay_time,'%Y-%m-%d %H:%i:%S') AS pay_time,bank_type_id,merchant_id,level_id,money FROM tb_o_point_order_info_{customeruservice.id%900} {condition};
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            lists = []
            for row in rows:
                if row['bank_type_id'] == 1:
                    row['bank_type'] = '邮储信用卡'
                elif row['bank_type_id'] == 2:
                    row['bank_type'] = '邮储借记卡'
                else:
                    row['bank_type'] = '其他'
                if row['merchant_id']:
                    merchant = Merchant.objects.get(id=row['merchant_id'])
                    row['merchant_name'] = merchant.short_name
                    row['level_name'] = merchant.level.name
                else:
                    row['merchant_name'] = ''
                    row['level_name'] = ''
                lists.append(row)
            data = {}
            data['counts'] = counts
            data['lists'] = lists
            data['page'] = page
            data['pages'] = pages
            data['pagesize'] = pagesize
            return Response(data, status=status.HTTP_200_OK)
        except:
            return Response({'error': '系统繁忙'}, status=status.HTTP_400_BAD_REQUEST)

    def customeruservice_order_map(self, request):
        try:
            level_id = self.request.query_params.get('level_id', None)
            customeruservice_id = self.request.query_params.get('customeruservice_id', None)
            bank_type = self.request.query_params.get('bank_type', None)
            pay_time_begin = self.request.query_params.get('pay_time_begin', None)
            pay_time_end = self.request.query_params.get('pay_time_end', None)
            amount_min = self.request.query_params.get('amount_min', None)
            amount_max = self.request.query_params.get('amount_max', None)
            search = self.request.query_params.get('search', None)
            longitude = self.request.query_params.get('longitude', None)
            latitude = self.request.query_params.get('latitude', None)
            max_distance = self.request.query_params.get('max_distance', 100)
            if not max_distance:
                return Response({'error': '请上传比例尺'}, status=status.HTTP_400_BAD_REQUEST)
            if not all([longitude, latitude]):
                return Response({'error': '请上传经纬度'}, status=status.HTTP_400_BAD_REQUEST)
            if int(Decimal(max_distance)) > 5000:
                return Response({'error': '比例尺最大范围为5公里'}, status=status.HTTP_400_BAD_REQUEST)
            if not customeruservice_id:
                return Response({'error': '请上传所属用户id'}, status=status.HTTP_400_BAD_REQUEST)
            try:
                customeruservice = CustomerUserVice.objects.get(id=customeruservice_id)
            except:
                return Response({'error': '无效用户id'}, status=status.HTTP_400_BAD_REQUEST)
            condition = f'WHERE customeruservice_id={customeruservice_id}'
            if level_id:
                level = Level.objects.filter(id=level_id).first()
                level_list = get_level_list(level)
                level_tuple = tuple(level_list)
                if len(level_tuple) == 1:
                    level_tuple = str(level_tuple).replace(',', '')
                condition = condition + f' AND level_id IN {level_tuple}'
            if pay_time_begin:
                condition = condition + f' AND pay_time >= "{pay_time_begin + " 00:00:00"}"'
            if pay_time_end:
                condition = condition + f' AND pay_time <= "{pay_time_end + " 23:59:59"}"'
            if amount_min:
                condition = condition + f' AND money>={amount_min}'
            if amount_max:
                condition = condition + f' AND money<={amount_max}'
            if bank_type:
                condition = condition + f' AND bank_type_id IN ({bank_type})'
            if search:
                merchant_list = Merchant.objects.filter(name__contains=search).values_list('id', flat=True)
                if merchant_list:
                    merchant_tuple = tuple(merchant_list)
                    if len(merchant_tuple) == 1:
                        merchant_tuple = str(merchant_tuple).replace(',', '')
                    condition = condition + f' AND merchant_id IN {merchant_tuple}'
            conn = pymysql.connect(host=settings.DATABASES['business_custmoer_order_data']['HOST'],
                                   port=settings.DATABASES['business_custmoer_order_data']['PORT'],
                                   database=settings.DATABASES['business_custmoer_order_data']['NAME'],
                                   user=settings.DATABASES['business_custmoer_order_data']['USER'],
                                   password=settings.DATABASES['business_custmoer_order_data']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            sql = f'''
            SELECT id,DATE_FORMAT(pay_time,'%Y-%m-%d %H:%i:%S') AS pay_time,bank_type_id,merchant_id,level_id,money FROM tb_o_point_order_info_{customeruservice.id%900} {condition};
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            merchant_id_list = []
            for row in rows:
                if row['merchant_id']:
                    merchant_id_list.append(row['merchant_id'])
            merchant_dict = {}
            if merchant_id_list:
                gcd_formula = "6371 * acos(least(greatest(cos(radians(%s)) * cos(radians(latitude)) * cos(radians(longitude) - radians(%s)) + sin(radians(%s)) * sin(radians(latitude)) , -1), 1))"
                sql = RawSQL(gcd_formula, (latitude, longitude, latitude))
                queryset = Merchant.objects.filter(id__in=merchant_id_list).annotate(distance=sql).filter(distance__lte=float(max_distance)/100).order_by(F('distance').asc(nulls_last=True))
                for merchant in queryset:
                    merchant_data = {}
                    merchant_data['id'] = merchant.id
                    merchant_data['order_info'] = []
                    merchant_data['latitude'] = merchant.latitude
                    merchant_data['longitude'] = merchant.longitude
                    merchant_data['merchant_name'] = merchant.name
                    merchant_data['count'] = 0
                    merchant_data['amount'] = Decimal('0')
                    distance = geodesic((merchant.latitude, merchant.longitude), (latitude, longitude)).m
                    if distance > 1000:
                        distance = f'{round(distance / 1000, 1)} km'
                    else:
                        distance = f'{int(distance)} m'
                    merchant_data['distance'] = distance
                    merchant_dict[merchant.id] = merchant_data
            lists = []
            if merchant_dict:
                for row in rows:
                    if row['bank_type_id'] == 1:
                        row['bank_type'] = '邮储信用卡'
                    elif row['bank_type_id'] == 2:
                        row['bank_type'] = '邮储借记卡'
                    else:
                        row['bank_type'] = '其他'
                    if row['merchant_id']:
                        merchant = Merchant.objects.get(id=row['merchant_id'])
                        row['merchant_name'] = merchant.short_name
                        row['level_name'] = merchant.level.name
                    else:
                        row['merchant_name'] = ''
                        row['level_name'] = ''
                    if row['merchant_id'] in merchant_dict:
                        merchant = merchant_dict[row['merchant_id']]
                        merchant['order_info'].append(row)
                        merchant['count'] += 1
                        merchant['amount'] += Decimal(row['money'])
                for key, value in merchant_dict.items():
                    lists.append(value)
            return Response({'data': lists}, status=status.HTTP_200_OK)
        except:
            return Response({'error': '系统繁忙'}, status=status.HTTP_400_BAD_REQUEST)


class CustomerUserViceInviterRecordModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__wyfMerchantID', 'sn')
    filter_class = CustomerUserViceInviterRecordFilter

    def get_queryset(self):
        queryset = CustomerUserViceInviterRecord.objects.all().order_by('-create_time')
        return queryset

    def get_serializer_class(self):
        return CustomerUserViceInviterRecordListModelSerializer


class JoinCustomizeActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('customeruservice__mobile', )
    filter_class = JoinCustomizeActivityFilter

    def get_queryset(self):
        sort = self.request.query_params.get('sort', None)
        queryset = JoinCustomizeActivity.objects.filter().order_by('-id')
        if sort == '1':
            queryset = queryset.order_by('count')
        if sort == '2':
            queryset = queryset.order_by('-count')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return JoinCustomizeActivityListModelSerializer
        else:
            return JoinCustomizeActivityRetrieveModelSerializer

    def export_joincustomizeactivity(self, request):
        user = self.request.iser
        is_join = self.request.query_params.get('is_join', None)
        join_time_begin = self.request.query_params.get('join_time_begin', None)
        join_time_end = self.request.query_params.get('join_time_end', None)
        search = self.request.query_params.get('search', None)
        count = self.request.query_params.get('count', None)
        condition = 'WHERE id >1'
        if search:
            condition = condition + f' AND customeruservice_id IN (SELECT id FROM tb_u_customer_user_vice WHERE mobile LIKE "%{search}%")'
        if join_time_begin:
            condition = condition + f' AND join_time >= "{join_time_begin + " 00:00:00"}"'
        if join_time_end:
            condition = condition + f' AND join_time <= "{join_time_end + " 23:59:59"}"'
        if is_join:
            condition = condition + f' AND is_join IN ({is_join})'
        if count:
            condition = condition + f' AND count IN ({count})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.is_psbc_card,mobile,wyf_gzh_openid,unionid FROM (SELECT a.*,b.activity_name FROM (SELECT id,DATE_FORMAT(join_time,'%Y-%m-%d %H:%i:%S')AS join_time,customeruservice_id,customizeactivity_id,is_join,count FROM tb_customize_activity_join {condition})AS a LEFT JOIN (SELECT id,activity_name FROM tb_customize_activity)AS b ON a.customizeactivity_id=b.id)AS c LEFT JOIN (SELECT id,wyf_gzh_openid,is_psbc_card,mobile,unionid FROM tb_u_customer_user_vice)AS d ON c.customeruservice_id=d.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT c.*,d.is_psbc_card,mobile,wyf_gzh_openid,unionid FROM (SELECT a.*,b.activity_name FROM (SELECT id,DATE_FORMAT(join_time,'%Y-%m-%d %H:%i:%S')AS join_time,customeruservice_id,customizeactivity_id,is_join,count FROM tb_customize_activity_join)AS a LEFT JOIN (SELECT id,activity_name FROM tb_customize_activity)AS b ON a.customizeactivity_id=b.id)AS c LEFT JOIN (SELECT id,wyf_gzh_openid,is_psbc_card,mobile,unionid FROM tb_u_customer_user_vice)AS d ON c.customeruservice_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', 'unionid', '活动名称', '任务状态', '任务进度', '领取任务时间', '手机号码', '是否微邮付公众号粉丝', '是否绑定邮储卡']
        cols = ['id', 'unionid', 'activity_name', 'is_join', 'count', 'join_time', 'mobile', 'wyf_gzh_openid', 'is_psbc_card']
        response = StreamingHttpResponse(self.get_joincustomizeactivity_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('消费有礼完成明细.csv'))
        return response

    def get_joincustomizeactivity_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_JOIN_ENUM = {
            0: '未报名',
            1: '进行中',
            2: '已完成'
        }
        IS_PSBC_CARD_ENUM = {
            0: '否',
            1: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_join':
                    tmp_str += '"%s",' % (IS_JOIN_ENUM[row[col]])
                elif col == 'is_psbc_card':
                    tmp_str += '"%s",' % (IS_PSBC_CARD_ENUM[row[col]])
                elif col == 'count':
                    if row[col]:
                        tmp_str += '"%s",' % f'第{row[col]}笔'
                    else:
                        tmp_str += '"%s",' % ''
                elif col == 'wyf_gzh_openid':
                    if row[col]:
                        tmp_str += '"%s",' % '是'
                    else:
                        tmp_str += '"%s",' % '否'
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def retrieve(self, request, *args, **kwargs):
        joincustomizeactivity = self.get_object()
        serializer = self.get_serializer(joincustomizeactivity)
        data = serializer.data
        if joincustomizeactivity.is_join == 0:
            order_info = {}
            order_info['list'] = []
            order_info['counts'] = 0
            order_info['page'] = self.request.query_params.get('page', 1)
            order_info['pages'] = 1
            order_info['pagesize'] = 10
            data['order_info'] = order_info
        else:
            customizeactivity = joincustomizeactivity.customizeactivity
            customizeactivitypayrules = customizeactivity.customizeactivitypayrules
            join_time = joincustomizeactivity.join_time
            available_end_time = customizeactivity.available_end_time
            total_count = customizeactivitypayrules.total_count
            amount = customizeactivitypayrules.amount
            draw_counts = customizeactivitypayrules.draw_counts.split(',')
            limit_merchant_count_by_day = customizeactivitypayrules.limit_merchant_count_by_day
            if customizeactivitypayrules.is_limit_merchant == 0:
                queryset = get_point_record_db_model(joincustomizeactivity.customeruservice_id).objects.filter(
                    pay_time__gte=join_time,
                    pay_time__lte=available_end_time,
                    amount__gte=amount,
                    customeruservice_id=joincustomizeactivity.customeruservice_id,
                    level__prov_id=7330
                ).values('shop_id').annotate(order_id=Min('id'))[0:total_count]
                if joincustomizeactivity.is_join == 1:
                    if len(queryset) >= total_count:
                        joincustomizeactivity.is_join = 2
                    joincustomizeactivity.count = len(queryset)
                    joincustomizeactivity.save()
                order_id_dict = {}
                i = 1
                for order in queryset:
                    order_id_dict[order['order_id']] = i
                    i += 1
            else:
                merchant_id_list = CustomizeActivityPayRulesLimitMerchant.objects.filter(
                    customizeactivity_id=customizeactivity.id).values_list('merchant_id', flat=True)
                queryset = get_point_record_db_model(joincustomizeactivity.customeruservice_id).objects.filter(
                    pay_time__gte=join_time,
                    pay_time__lte=available_end_time,
                    amount__gte=amount,
                    customeruservice_id=joincustomizeactivity.customeruservice_id,
                    merchant_id__in=merchant_id_list
                )
                order_id_list = []
                check_date_order = {}
                check_order_num = 0
                for order in queryset:
                    if check_order_num >= total_count:
                        break
                    pay_time = f'{order.pay_time.strftime("%Y%m%d")}{order.merchant_id}'
                    if pay_time not in check_date_order:
                        check_date_order[pay_time] = 0
                    if check_date_order[pay_time] >= limit_merchant_count_by_day:
                        continue
                    order_id_list.append(order.id)
                    check_date_order[pay_time] = check_date_order[pay_time] + 1
                    check_order_num += 1
                if joincustomizeactivity.is_join == 1:
                    if len(order_id_list) >= total_count:
                        joincustomizeactivity.is_join = 2
                    joincustomizeactivity.count = len(order_id_list)
                    joincustomizeactivity.save()
                order_id_dict = {}
                i = 1
                for order in order_id_list:
                    order_id_dict[order] = i
                    i += 1
            queryset = get_point_record_db_model(joincustomizeactivity.customeruservice_id).objects.filter(
                pay_time__gte=join_time,
                pay_time__lte=available_end_time,
                customeruservice_id=joincustomizeactivity.customeruservice_id,
            ).order_by('-id')
            page = self.paginate_queryset(queryset)
            schedule_list = []
            for query in page:
                try:
                    order_data = {}
                    order_data['pay_time'] = query.pay_time.strftime("%Y-%m-%d %H:%M:%S")
                    order_data['amount'] = query.amount
                    order_data['id'] = query.id
                    if query.bank_type == 'PSBC_DEBIT':
                        order_data['bank_type_sign_str'] = '邮储借记卡'
                    elif query.bank_type == 'PSBC_CREDIT':
                        order_data['bank_type_sign_str'] = '邮储信用卡'
                        order_data['bank_type_sign'] = 2
                    else:
                        order_data['bank_type_sign_str'] = '其他'
                        order_data['bank_type_sign'] = 3
                    merchant = query.merchant
                    if merchant:
                        order_data['merchant_name'] = merchant.name
                        order_data['merchant_id'] = merchant.id
                    else:
                        order_data['merchant_name'] = ''
                        order_data['merchant_id'] = ''
                    if query.id in order_id_dict:
                        order_data['count'] = f'第{order_id_dict[query.id]}笔'
                        order_data['is_effective'] = 1
                        if str(order_id_dict[query.id]) in draw_counts:
                            if not query.customizeactivityflipcards:
                                customizeactivityrules = CustomizeActivityRules.objects.get(customizeactivity_id=customizeactivity.id, index=order_id_dict[query.id])
                                identify = f'{str(datetime.datetime.now()).replace("-", "").replace(":", "").replace(" ", "").replace(".", "")}{"%06d" % randint(0, 999999)}{"%07d" % joincustomizeactivity.customeruservice_id}'
                                instance_data = dict()
                                instance_data['identify'] = identify
                                instance_data['customeruservice_id'] = joincustomizeactivity.customeruservice_id
                                instance_data['mobile'] = joincustomizeactivity.customeruservice.mobile
                                instance_data['gears'] = 1
                                instance_data['customizeactivity_id'] = customizeactivity.id
                                if customizeactivityrules.prize_type in [1, 2]:
                                    instance_data['alidiscountcoupon_id'] = customizeactivityrules.alidiscountcoupon_id
                                    instance_data['win_type'] = customizeactivityrules.prize_type
                                elif customizeactivityrules.prize_type in [3, 6]:
                                    instance_data['cashactivity_id'] = customizeactivityrules.cashactivity_id
                                    instance_data['win_type'] = customizeactivityrules.prize_type
                                instance = CustomizeActivityFlipCARDS.objects.create(**instance_data)
                                if instance.win_type in [1, 2]:
                                    instance.open_status = 2
                                    instance.open_time = datetime.datetime.now()
                                    instance.save()
                                    alidiscountcoupon = instance.alidiscountcoupon
                                    if alidiscountcoupon.remain_number <= 0:
                                        x = list(customizeactivity.customizeactivityrules_set.filter(alidiscountcoupon__remain_number__gt=0).values_list('alidiscountcoupon_id', flat=True))
                                        if x:
                                            shuffle(x)
                                            alidiscountcoupon = AliDiscountCoupon.objects.get(id=x[0])
                                            instance.alidiscountcoupon_id = alidiscountcoupon.id
                                            instance.save()
                                        else:
                                            instance.open_status = 3
                                            instance.win_type = 4
                                            instance.open_time = datetime.datetime.now()
                                            instance.save()
                                    activity_id = alidiscountcoupon.id
                                    while True:
                                        alidiscountcoupon = AliDiscountCoupon.objects.get(id=activity_id)
                                        activity_origin_send_count = alidiscountcoupon.send_count
                                        activity_origin_remain_number = alidiscountcoupon.remain_number
                                        activity_new_send_count = activity_origin_send_count + 1
                                        activity_new_remain_number = activity_origin_remain_number - 1
                                        if activity_new_remain_number < 0:
                                            instance.open_status = 3
                                            instance.win_type = 4
                                            instance.open_time = datetime.datetime.now()
                                            instance.save()
                                        result = AliDiscountCoupon.objects.filter(id=activity_id,send_count=activity_origin_send_count).update(send_count=activity_new_send_count, remain_number=activity_new_remain_number)
                                        if result == 0:
                                            continue
                                        break
                                    alidiscountcouponinfo_data = {}
                                    alidiscountcouponinfo_data['alidiscountcoupon_id'] = alidiscountcoupon.id
                                    alidiscountcouponinfo_data['level_id'] = instance.level_id
                                    alidiscountcouponinfo_data['status'] = 0
                                    alidiscountcouponinfo_data['template_id'] = alidiscountcoupon.template_id
                                    out_biz_no = f'{str(datetime.datetime.now()).replace("-", "").replace(":", "").replace(" ", "").replace(".", "")}{"%06d" % randint(0, 999999)}{"%07d" % joincustomizeactivity.customeruservice_id}'
                                    alidiscountcouponinfo_data['out_biz_no'] = out_biz_no
                                    alidiscountcouponinfo_data['gmt_distributed'] = datetime.datetime.now()
                                    alidiscountcouponinfo, crete = AliDiscountCouponInfo.objects.get_or_create(defaults=alidiscountcouponinfo_data, out_biz_no=out_biz_no)
                                    instance.alidiscountcouponinfo_id = alidiscountcouponinfo.id
                                    instance.save()
                                else:
                                    instance.open_status = 2
                                    instance.open_time = datetime.datetime.now()
                                    instance.save()
                                    cashactivity = instance.cashactivity
                                    cashactivityrule = CashActivityRule.objects.filter(cash_activity_id=cashactivity.id, is_delete=0).first()
                                    if cashactivityrule.amount > cashactivity.remain_amount:
                                        instance.open_status = 3
                                        instance.win_type = 4
                                        instance.open_time = datetime.datetime.now()
                                        instance.save()
                                    amount = cashactivityrule.amount
                                    activity_id = cashactivity.id
                                    while True:
                                        activity = CashActivity.objects.get(id=activity_id)
                                        activity_origin_take_amount = activity.take_amount
                                        activity_origin_remain_amount = activity.remain_amount
                                        if activity_origin_remain_amount < amount:
                                            instance.open_status = 3
                                            instance.win_type = 4
                                            instance.open_time = datetime.datetime.now()
                                            instance.save()
                                        activity_new_take_amount = activity_origin_take_amount + amount
                                        activity_new_remain_amount = activity_origin_remain_amount - amount
                                        result = CashActivity.objects.filter(take_amount=activity_origin_take_amount, remain_amount=activity_origin_remain_amount, id=activity_id).update(take_amount=activity_new_take_amount, remain_amount=activity_new_remain_amount)
                                        if result == 0:
                                            continue
                                        break
                                    date_now = datetime.datetime.now()
                                    activity_data = {}
                                    activity_data['amount'] = amount
                                    activity_data['create_time'] = date_now
                                    activity_data['cash_activity_id'] = activity.id
                                    activity_data['level_id'] = instance.level_id
                                    activity_data['state'] = 1
                                    identify = random_str()
                                    nonce_str = random_str()
                                    activity_data['identify'] = identify
                                    activity_data['nonce_str'] = nonce_str
                                    activity_data['desc'] = activity.name
                                    activity_data['partner_trade_no'] = f'{date_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}{"%07d" % joincustomizeactivity.customeruservice_id}'
                                    activity_data['expired_time'] = date_now + datetime.timedelta(hours=11)
                                    activity_dat, create = CashActivityData.objects.get_or_create(defaults=activity_data, identify=identify)
                                    instance.cashactivitydata_id = activity_dat.id
                                    instance.amount = amount
                                    instance.save()
                                query.customizeactivityflipcards_id = instance.id
                                query.save()
                            else:
                                instance = query.customizeactivityflipcards
                            if instance.win_type in [1, 2]:
                                order_data['open_status'] = instance.open_status
                                order_data['open_status_str'] = instance.get_open_status_display()
                                order_data['activity_name'] = instance.alidiscountcoupon.activity_name
                            else:
                                order_data['open_status'] = instance.open_status
                                order_data['open_status_str'] = instance.get_open_status_display()
                                order_data['activity_name'] = instance.cashactivity.name
                        else:
                            order_data['open_status'] = ''
                            order_data['open_status_str'] = ''
                            order_data['activity_name'] = ''
                    else:
                        order_data['count'] = ''
                        order_data['is_effective'] = 0
                        order_data['open_status'] = ''
                        order_data['open_status_str'] = ''
                        order_data['activity_name'] = ''
                    schedule_list.append(order_data)
                except Exception as e:
                    pass
            order_info = {}
            order_info['list'] = schedule_list
            order_info['counts'] = len(queryset)
            order_info['page'] = self.request.query_params.get('page', 1)
            order_info['pages'] = math.ceil(len(queryset)/10)
            order_info['pagesize'] = 10
            data['order_info'] = order_info
        return Response(data, status=status.HTTP_200_OK)


class BindCardOrderModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('customeruservice__mobile', )
    filter_class = BindCardOrderFilter

    def get_queryset(self):
        queryset = BindCardOrder.objects.filter().order_by('-id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return BindCardOrderListModelSerializer
        else:
            return BindCardOrderRetrieveModelSerializer

    def export_bindcardorder(self, request):
        received_time_begin = self.request.query_params.get('received_time_begin', None)
        received_time_end = self.request.query_params.get('received_time_end', None)
        order_status = self.request.query_params.get('order_status', None)
        prize_status = self.request.query_params.get('prize_status', None)
        bank_type = self.request.query_params.get('bank_type', None)
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        search = self.request.query_params.get('search', None)
        condition = 'WHERE id!=0'
        if search:
            condition = condition + f' AND customeruservice_id IN (SELECT id FROM tb_u_customer_user_vice WHERE mobile LIKE "%{search}%")'
        if received_time_begin:
            condition = condition + f' AND customizeactivityflipcards_id IN (SELECT id FROM tb_customize_activity_flip_cards WHERE received_time >= "{received_time_begin + " 00:00:00"}")'
        if received_time_end:
            condition = condition + f' AND customizeactivityflipcards_id IN (SELECT id FROM tb_customize_activity_flip_cards WHERE received_time <= "{received_time_end + " 23:59:59"}")'
        if order_status:
            condition = condition + f' AND order_status IN ({order_status})'
        if prize_status:
            condition = condition + f' AND prize_status IN ({prize_status})'
        if bank_type:
            condition = condition + f' AND bank_type_sign IN ({bank_type})'
        if customizeactivity_id:
            condition = condition + f' AND customizeactivity_id IN ({customizeactivity_id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT m.*,n.city_name,district_name,branch_name FROM (SELECT k.*,l.name,wyfMerchantID,level_id FROM (SELECT i.*,j.merchant_id FROM (SELECT g.*,h.activity_name FROM (SELECT e.*,f.received_time,alidiscountcoupon_id FROM (SELECT c.*,d.bank_name FROM (SELECT a.*,b.mobile,unionid FROM (SELECT id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,description,bank_type,order_status,prize_status,customeruservice_id,customizeactivityflipcards_id,business_qrcode FROM tb_o_bind_card_order {condition})AS a LEFT JOIN (SELECT id,mobile,unionid FROM tb_u_customer_user_vice)AS b ON a.customeruservice_id=b.id)AS c LEFT JOIN (SELECT bank_code,bank_name FROM tb_bank_type)AS d ON c.bank_type=d.bank_code)AS e LEFT JOIN (SELECT id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,alidiscountcoupon_id FROM tb_customize_activity_flip_cards)AS f ON e.customizeactivityflipcards_id=f.id)AS g LEFT JOIN (SELECT id,activity_name FROM tb_ali_discount_coupon)AS h ON g.alidiscountcoupon_id=h.id)AS i LEFT JOIN (SELECT merchant_id,sn FROM tb_merchant_business_qrcode)AS j ON i.business_qrcode=j.sn)AS k LEFT JOIN (SELECT id,name,wyfMerchantID,level_id FROM tb_merchant)AS l ON k.merchant_id=l.id)AS m LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS n ON m.level_id=n.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT m.*,n.city_name,district_name,branch_name FROM (SELECT k.*,l.name,wyfMerchantID,level_id FROM (SELECT i.*,j.merchant_id FROM (SELECT g.*,h.activity_name FROM (SELECT e.*,f.received_time,alidiscountcoupon_id FROM (SELECT c.*,d.bank_name FROM (SELECT a.*,b.mobile,unionid FROM (SELECT id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,description,bank_type,order_status,prize_status,customeruservice_id,customizeactivityflipcards_id,business_qrcode FROM tb_o_bind_card_order)AS a LEFT JOIN (SELECT id,mobile,unionid FROM tb_u_customer_user_vice)AS b ON a.customeruservice_id=b.id)AS c LEFT JOIN (SELECT bank_code,bank_name FROM tb_bank_type)AS d ON c.bank_type=d.bank_code)AS e LEFT JOIN (SELECT id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,alidiscountcoupon_id FROM tb_customize_activity_flip_cards)AS f ON e.customizeactivityflipcards_id=f.id)AS g LEFT JOIN (SELECT id,activity_name FROM tb_ali_discount_coupon)AS h ON g.alidiscountcoupon_id=h.id)AS i LEFT JOIN (SELECT merchant_id,sn FROM tb_merchant_business_qrcode)AS j ON i.business_qrcode=j.sn)AS k LEFT JOIN (SELECT id,name,wyfMerchantID,level_id FROM tb_merchant)AS l ON k.merchant_id=l.id)AS m LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS n ON m.level_id=n.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', 'unionid', '活动描述', '活动参与时间', '支付方式', '支付状态', '领券状态', '领券情况', '手机号码', '领取时间', '参与活动来源', '微邮付商户号', '商户名称', '市公司', '区县', '网点']
        cols = ['id', 'unionid', 'description', 'create_time', 'bank_name', 'order_status', 'prize_status', 'activity_name', 'mobile', 'received_time', 'source', 'wyfMerchantID', 'name', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_bindcardorder_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('验卡有礼完成明细.csv'))
        return response

    def get_bindcardorder_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        ORDER_STATUS_ENUM = {
            1: '待支付',
            2: '已支付',
            3: '支付失败',
            4: '已退款',
            5: '退款失败',
        }
        PRIZE_STATUS_ENUM = {
            1: '初始状态',
            2: '待领取',
            3: '已领取',
            4: '领取失败',
            5: '已作废',
            6: '待抽奖',
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'order_status':
                    tmp_str += '"%s",' % (ORDER_STATUS_ENUM[row[col]])
                elif col == 'source':
                    if row['business_qrcode']:
                        tmp_str += '"%s",' % ('商圈码扫码')
                    else:
                        tmp_str += '"%s",' % ('其他渠道')
                elif col == 'prize_status':
                    tmp_str += '"%s",' % (PRIZE_STATUS_ENUM[row[col]])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class SpeedLoanUserInfoModelViewSet(ModelViewSet):

    def export_loanuserinfo(self, request):
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'''
        SELECT c.*,d.prov_name,city_name,district_name,branch_name FROM (SELECT a.*,b.name FROM (SELECT * FROM tb_speed_loan_user_info WHERE id>4)AS a LEFT JOIN (SELECT id,name FROM tb_speed_loan_industry)AS b ON a.industry_id=b.id)AS c LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;
        '''
        cursor.execute(sql)
        ziduan = ['id', '客户手机号码', '客户姓名', '年龄', '经营行业', '所在地区(省)', '所在地区(市)', '所在地区(区)', '详细地址', '经营年限', '是否有固定房产', '微邮付商户号(参考)',
                  '所属市公司', '所属区县', '所属网点']
        cols = ['id', 'mobile', 'realname', 'age', 'name', 'province', 'city', 'district', 'address', 'business_limit', 'is_house', 'business_no', 'city_name',
                'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_loanuserinfo_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('极速贷商户信息.csv'))
        return response

    def get_loanuserinfo_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_HOUSE_ENUM = {
            0: '否',
            1: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_house':
                    tmp_str += '"%s",' % (IS_HOUSE_ENUM[row[col]])
                elif col in ['age']:
                    tmp_str += '"%s"岁,' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col in ['business_limit']:
                    tmp_str += '"%s"年,' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"